﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using System.Data.OleDb;

namespace QuanLiHocSinh
{
    public partial class TraCuu : Form
    {
        OleDbConnection connect;
        public TraCuu()
        {
            InitializeComponent();
            //===========================================================================================


            connect = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=CSDL.mdb");

            if (connect != null)
            {
                connect.Open();
                string lop = "select * from lop";
                OleDbCommand cm = new OleDbCommand(lop, connect);
                OleDbDataReader rd = cm.ExecuteReader();
                while (rd.Read())
                {
                    comboBox1.Items.Add(rd.GetString(0));
                }
            }
            else
            {
                MessageBox.Show("Không kết nối được với CSDL", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
            }

        }

        private void button2_Click(object sender, EventArgs e)
        {
            //===========================
            listView1.Items.Clear();
            textBox2.Text = "";
            textBox3.Text = "";
            textBox4.Text = "";
            textBox5.Text = "";
            textBox7.Text = "";
            comboBox1.Text = "";
            //===========================
            if (textBox1.Text == "")
            {
                MessageBox.Show("Chưa nhập mã học sinh!", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
            }
            else
            {
                string get_hs = "select * from hocsinh where MaHS = '" + textBox1.Text + "'";
                OleDbCommand comman = new OleDbCommand(get_hs, connect);
                OleDbDataReader reader = comman.ExecuteReader();
                if (!reader.Read())
                {
                    MessageBox.Show("Không tìm thấy học sinh nào!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
                    return;
                }
                comman = new OleDbCommand(get_hs, connect);
                reader = comman.ExecuteReader();
                while (reader.Read())
                {
                    listView1.Items.Add("1");
                    listView1.Items[0].SubItems.Add(reader.GetString(1));
                    listView1.Items[0].SubItems.Add(reader.GetString(5));
                    textBox2.Text = reader.GetString(1);
                    textBox3.Text = reader.GetString(2);
                    textBox4.Text = reader.GetDateTime(3).ToString();
                    textBox5.Text = reader.GetString(4);
                    comboBox1.Text = reader.GetString(5);
                    textBox7.Text = reader.GetString(6);
                    //====================TB hk1================
                    float tong_TB = 0;
                    string mon = "select MaMon from monhoc";
                    OleDbCommand cmm = new OleDbCommand(mon, connect);
                    OleDbDataReader rdd = cmm.ExecuteReader();
                    while (rdd.Read())
                    {
                        string kq = "select DiemTB from " + rdd.GetString(0) + " where HoTen = '" + reader.GetString(1) + "' and Lop = '" + reader.GetString(5) + "' and HocKy = " + 1 + "";
                        OleDbCommand cmmm = new OleDbCommand(kq, connect);
                        OleDbDataReader rddd = cmmm.ExecuteReader();

                        while (rddd.Read())
                        {
                            tong_TB += rddd.GetFloat(0);
                        }
                    }
                    float tong_mon = 0;
                    float dat_mon = 0;
                    string tm = "select count(*) from monhoc";
                    cmm = new OleDbCommand(tm, connect);
                    rdd = cmm.ExecuteReader();
                    while (rdd.Read())
                    {
                        tong_mon = (float)rdd.GetInt32(0);
                    }
                    float diem_TB = tong_TB / tong_mon;
                    listView1.Items[0].SubItems.Add(diem_TB.ToString());
                    //====================TB hk2================
                    tong_TB = 0;
                    mon = "select MaMon from monhoc";
                    cmm = new OleDbCommand(mon, connect);
                    rdd = cmm.ExecuteReader();
                    while (rdd.Read())
                    {
                        string kq = "select DiemTB from " + rdd.GetString(0) + " where HoTen = '" + reader.GetString(1) + "' and Lop = '" + reader.GetString(5) + "' and HocKy = " + 2 + "";
                        OleDbCommand cmmm = new OleDbCommand(kq, connect);
                        OleDbDataReader rddd = cmmm.ExecuteReader();

                        while (rddd.Read())
                        {
                            tong_TB += rddd.GetFloat(0);
                        }
                    }
                    tong_mon = 0;
                    dat_mon = 0;
                    tm = "select count(*) from monhoc";
                    cmm = new OleDbCommand(tm, connect);
                    rdd = cmm.ExecuteReader();
                    while (rdd.Read())
                    {
                        tong_mon = (float)rdd.GetInt32(0);
                    }
                    diem_TB = tong_TB / tong_mon;
                    listView1.Items[0].SubItems.Add(diem_TB.ToString());
                    //======================================
                }
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //===========================
            listView1.Items.Clear();
            textBox1.Text = "";
            textBox3.Text = "";
            textBox4.Text = "";
            textBox5.Text = "";
            textBox7.Text = "";
            comboBox1.Text = "";
            //===========================
            if (textBox2.Text == "")
            {
                MessageBox.Show("Chưa nhập tên học sinh!", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
            }
            else
            {
                string get_hs = "select * from hocsinh where HoTen = '" + textBox2.Text + "'";
                OleDbCommand comman = new OleDbCommand(get_hs, connect);
                OleDbDataReader reader = comman.ExecuteReader();
                if (!reader.Read())
                {
                    MessageBox.Show("Không tìm thấy học sinh nào!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
                    return;
                }
                comman = new OleDbCommand(get_hs, connect);
                reader = comman.ExecuteReader();
                while (reader.Read())
                {
                    listView1.Items.Add("1");
                    listView1.Items[0].SubItems.Add(reader.GetString(1));
                    listView1.Items[0].SubItems.Add(reader.GetString(5));
                    textBox1.Text = reader.GetString(0);
                    textBox3.Text = reader.GetString(2);
                    textBox4.Text = reader.GetDateTime(3).ToString();
                    textBox5.Text = reader.GetString(4);
                    comboBox1.Text = reader.GetString(5);
                    textBox7.Text = reader.GetString(6);
                    //====================TB hk1================
                    float tong_TB = 0;
                    string mon = "select MaMon from monhoc";
                    OleDbCommand cmm = new OleDbCommand(mon, connect);
                    OleDbDataReader rdd = cmm.ExecuteReader();
                    while (rdd.Read())
                    {
                        string kq = "select DiemTB from " + rdd.GetString(0) + " where HoTen = '" + reader.GetString(1) + "' and Lop = '" + reader.GetString(5) + "' and HocKy = " + 1 + "";
                        OleDbCommand cmmm = new OleDbCommand(kq, connect);
                        OleDbDataReader rddd = cmmm.ExecuteReader();

                        while (rddd.Read())
                        {
                            tong_TB += rddd.GetFloat(0);
                        }
                    }
                    float tong_mon = 0;
                    float dat_mon = 0;
                    string tm = "select count(*) from monhoc";
                    cmm = new OleDbCommand(tm, connect);
                    rdd = cmm.ExecuteReader();
                    while (rdd.Read())
                    {
                        tong_mon = (float)rdd.GetInt32(0);
                    }
                    float diem_TB = tong_TB / tong_mon;
                    listView1.Items[0].SubItems.Add(diem_TB.ToString());
                    //====================TB hk2================
                    tong_TB = 0;
                    mon = "select MaMon from monhoc";
                    cmm = new OleDbCommand(mon, connect);
                    rdd = cmm.ExecuteReader();
                    while (rdd.Read())
                    {
                        string kq = "select DiemTB from " + rdd.GetString(0) + " where HoTen = '" + reader.GetString(1) + "' and Lop = '" + reader.GetString(5) + "' and HocKy = " + 2 + "";
                        OleDbCommand cmmm = new OleDbCommand(kq, connect);
                        OleDbDataReader rddd = cmmm.ExecuteReader();

                        while (rddd.Read())
                        {
                            tong_TB += rddd.GetFloat(0);
                        }
                    }
                    tong_mon = 0;
                    dat_mon = 0;
                    tm = "select count(*) from monhoc";
                    cmm = new OleDbCommand(tm, connect);
                    rdd = cmm.ExecuteReader();
                    while (rdd.Read())
                    {
                        tong_mon = (float)rdd.GetInt32(0);
                    }
                    diem_TB = tong_TB / tong_mon;
                    listView1.Items[0].SubItems.Add(diem_TB.ToString());
                    //======================================
                }
            }
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            //===========================
            listView1.Items.Clear();
            textBox1.Text = "";
            textBox2.Text = "";
            textBox3.Text = "";
            textBox4.Text = "";
            textBox5.Text = "";
            textBox7.Text = "";
            //===========================
            int i = 0;
            int stt = 1;
            string get_hs = "select * from hocsinh where Lop = '" + comboBox1.Text + "'";
            OleDbCommand comman = new OleDbCommand(get_hs, connect);
            OleDbDataReader reader = comman.ExecuteReader();
            if (!reader.Read())
            {
                MessageBox.Show("Không tìm thấy học sinh nào!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
                return;
            }
            comman = new OleDbCommand(get_hs, connect);
            reader = comman.ExecuteReader();
            while (reader.Read())
            {
                listView1.Items.Add(stt.ToString());
                listView1.Items[i].SubItems.Add(reader.GetString(1));
                listView1.Items[i].SubItems.Add(reader.GetString(5));
                //====================TB hk1================
                float tong_TB = 0;
                string mon = "select MaMon from monhoc";
                OleDbCommand cmm = new OleDbCommand(mon, connect);
                OleDbDataReader rdd = cmm.ExecuteReader();
                while (rdd.Read())
                {
                    string kq = "select DiemTB from " + rdd.GetString(0) + " where HoTen = '" + reader.GetString(1) + "' and Lop = '" + reader.GetString(5) + "' and HocKy = " + 1 + "";
                    OleDbCommand cmmm = new OleDbCommand(kq, connect);
                    OleDbDataReader rddd = cmmm.ExecuteReader();

                    while (rddd.Read())
                    {
                        tong_TB += rddd.GetFloat(0);
                    }
                }
                float tong_mon = 0;
                float dat_mon = 0;
                string tm = "select count(*) from monhoc";
                cmm = new OleDbCommand(tm, connect);
                rdd = cmm.ExecuteReader();
                while (rdd.Read())
                {
                    tong_mon = (float)rdd.GetInt32(0);
                }
                float diem_TB = tong_TB / tong_mon;
                listView1.Items[i].SubItems.Add(diem_TB.ToString());
                //====================TB hk2================
                tong_TB = 0;
                mon = "select MaMon from monhoc";
                cmm = new OleDbCommand(mon, connect);
                rdd = cmm.ExecuteReader();
                while (rdd.Read())
                {
                    string kq = "select DiemTB from " + rdd.GetString(0) + " where HoTen = '" + reader.GetString(1) + "' and Lop = '" + reader.GetString(5) + "' and HocKy = " + 2 + "";
                    OleDbCommand cmmm = new OleDbCommand(kq, connect);
                    OleDbDataReader rddd = cmmm.ExecuteReader();

                    while (rddd.Read())
                    {
                        tong_TB += rddd.GetFloat(0);
                    }
                }
                tong_mon = 0;
                dat_mon = 0;
                tm = "select count(*) from monhoc";
                cmm = new OleDbCommand(tm, connect);
                rdd = cmm.ExecuteReader();
                while (rdd.Read())
                {
                    tong_mon = (float)rdd.GetInt32(0);
                }
                diem_TB = tong_TB / tong_mon;
                listView1.Items[i].SubItems.Add(diem_TB.ToString());
                //======================================
                i++;
                stt++;
            }



        }


        int get_intdex(ListView lv)
        {
            for (int i = 0; i < lv.Items.Count; i++)
            {
                if (lv.Items[i].Selected)
                {
                    return i;
                }
            }
            return 0;
        }


        private void listView1_SelectedIndexChanged(object sender, EventArgs e)
        {
            //===========================
            textBox1.Text = "";
            textBox2.Text = "";
            textBox3.Text = "";
            textBox4.Text = "";
            textBox5.Text = "";
            textBox7.Text = "";
            //===========================

            string get_hs = "select * from hocsinh where HoTen = '" + listView1.Items[get_intdex(listView1)].SubItems[1].Text + "'";
            OleDbCommand comman = new OleDbCommand(get_hs, connect);
            OleDbDataReader reader = comman.ExecuteReader();
            while (reader.Read())
            {
                textBox1.Text = reader.GetString(0);
                textBox2.Text = reader.GetString(1);
                textBox3.Text = reader.GetString(2);
                textBox4.Text = reader.GetDateTime(3).ToString();
                textBox5.Text = reader.GetString(4);
                textBox7.Text = reader.GetString(6);
            }

        }

        private void button3_Click(object sender, EventArgs e)
        {
            this.Close();
        }


    }
}
